using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;
using Pms.Utils;
using Biblioteca.Utils;
using Biblioteca.DataBase;
/*
 * 
 * Autor      : Jose Jornando de Carvalho Junior    
 * Data       : 28/03/2010 10:46:39
 * Descricao  : 
 * Alteracoes : 
 * 
 */

namespace Serix
{
   public class CUsuarios
   {
       private string COD_USU ;
       private string IDT_SETOR ;
       private string STATUS ;
       private string LOGIN ;
       private string NOME ;
       private string MAIL ;
       private string OBS ;
       private string TELEFONE ;
       private string CARGO ;
       private string SENHA ;

	   /// <sumary>
       /// Construtor da classe usuarios
       /// <sumary>
       public CUsuarios()
       {
   
       }

	   /// <sumary>
       /// Construtor da classe Usuarios
       /// <sumary>
       public CUsuarios( string COD_USU, string IDT_SETOR, string STATUS, string LOGIN, string NOME, string MAIL, string OBS, string TELEFONE, string CARGO, string SENHA )
       {
          this.COD_USU = COD_USU;
          this.IDT_SETOR = IDT_SETOR;
          this.STATUS = STATUS;
          this.LOGIN = LOGIN;
          this.NOME = NOME;
          this.MAIL = MAIL;
          this.OBS = OBS;
          this.TELEFONE = TELEFONE;
          this.CARGO = CARGO;
          this.SENHA = SENHA;
       }
	   
	   #region Propriedades
     	/// <sumary>
        /// 
        /// <sumary>
        public string Cod_usu 
		{
		   get
		   {
		       return this.COD_USU;
		   }
		   set
		   {
		       this.COD_USU = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Idt_setor 
		{
		   get
		   {
		       return this.IDT_SETOR;
		   }
		   set
		   {
		       this.IDT_SETOR = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Status 
		{
		   get
		   {
		       return this.STATUS;
		   }
		   set
		   {
		       this.STATUS = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Login 
		{
		   get
		   {
		       return this.LOGIN;
		   }
		   set
		   {
		       this.LOGIN = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Nome 
		{
		   get
		   {
		       return this.NOME;
		   }
		   set
		   {
		       this.NOME = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Mail 
		{
		   get
		   {
		       return this.MAIL;
		   }
		   set
		   {
		       this.MAIL = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Obs 
		{
		   get
		   {
		       return this.OBS;
		   }
		   set
		   {
		       this.OBS = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Telefone 
		{
		   get
		   {
		       return this.TELEFONE;
		   }
		   set
		   {
		       this.TELEFONE = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Cargo 
		{
		   get
		   {
		       return this.CARGO;
		   }
		   set
		   {
		       this.CARGO = value;
		   }
		}
     	/// <sumary>
        /// 
        /// <sumary>
        public string Senha 
		{
		   get
		   {
		       return this.SENHA;
		   }
		   set
		   {
		       this.SENHA = value;
		   }
		}
	    #endregion Propriedades
	
	    /// <summary>
        /// Inserindo um novo Usuarios.
        /// </summary>
        public void Inserir(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"insert into Serix.SEG_USUARIOS
			                  ( COD_USU, IDT_SETOR, STATUS, LOGIN, NOME, MAIL, OBS, TELEFONE, CARGO, SENHA )
						   values
						      ( :COD_USU, :IDT_SETOR, :STATUS, :LOGIN, :NOME, :MAIL, :OBS, :TELEFONE, :CARGO, :SENHA )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("COD_USU",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_SETOR",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("STATUS",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("LOGIN",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("NOME",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("MAIL",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("OBS",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("TELEFONE",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("CARGO",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("SENHA",OracleType.VarChar)); 
				
                command.Parameters["COD_USU"].Value = this.COD_USU;
                command.Parameters["IDT_SETOR"].Value = this.IDT_SETOR;
                command.Parameters["STATUS"].Value = this.STATUS;
                command.Parameters["LOGIN"].Value = this.LOGIN;
                command.Parameters["NOME"].Value = this.NOME;
                command.Parameters["SENHA"].Value = this.SENHA;
                if (String.IsNullOrEmpty(this.MAIL))
                {
                    command.Parameters["MAIL"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["MAIL"].Value = this.MAIL;
                }  
                if (String.IsNullOrEmpty(this.OBS))
                {
                    command.Parameters["OBS"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["OBS"].Value = this.OBS;
                }  
                if (String.IsNullOrEmpty(this.TELEFONE))
                {
                    command.Parameters["TELEFONE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["TELEFONE"].Value = this.TELEFONE;
                }  
                if (String.IsNullOrEmpty(this.CARGO))
                {
                    command.Parameters["CARGO"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["CARGO"].Value = this.CARGO;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	    /// <summary>
        /// Alterando um Usuarios.
        /// </summary>
        public void Alterar(string Login)
        {
		    OracleCommand command = new OracleCommand();
            OracleTransaction trans;

            string SQL = @"update Serix.SEG_USUARIOS set 
			                  ( IDT_SETOR = :IDT_SETOR, STATUS = :STATUS, LOGIN = :LOGIN, NOME = :NOME, MAIL = :MAIL, OBS = :OBS, TELEFONE = :TELEFONE, CARGO = :CARGO, SENHA = :SENHA )
						   where
						      ( COD_USU = :COD_USU )";
     		
			try
			{	
				command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
				
				command.Parameters.Add(new OracleParameter("COD_USU",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("IDT_SETOR",OracleType.Number)); 
				command.Parameters.Add(new OracleParameter("STATUS",OracleType.Char)); 
				command.Parameters.Add(new OracleParameter("LOGIN",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("NOME",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("MAIL",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("OBS",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("TELEFONE",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("CARGO",OracleType.VarChar)); 
				command.Parameters.Add(new OracleParameter("SENHA",OracleType.VarChar)); 
				
                command.Parameters["COD_USU"].Value = this.COD_USU;
                command.Parameters["IDT_SETOR"].Value = this.IDT_SETOR;
                command.Parameters["STATUS"].Value = this.STATUS;
                command.Parameters["LOGIN"].Value = this.LOGIN;
                command.Parameters["NOME"].Value = this.NOME;
                command.Parameters["SENHA"].Value = this.SENHA;
                if (String.IsNullOrEmpty(this.MAIL))
                {
                    command.Parameters["MAIL"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["MAIL"].Value = this.MAIL;
                }  
                if (String.IsNullOrEmpty(this.OBS))
                {
                    command.Parameters["OBS"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["OBS"].Value = this.OBS;
                }  
                if (String.IsNullOrEmpty(this.TELEFONE))
                {
                    command.Parameters["TELEFONE"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["TELEFONE"].Value = this.TELEFONE;
                }  
                if (String.IsNullOrEmpty(this.CARGO))
                {
                    command.Parameters["CARGO"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["CARGO"].Value = this.CARGO;
                }  
				command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
		}
		
	     /// <summary>
        /// Exclui um  Usuarios.
        /// </summary>
        public void Excluir(string Login)
        {
            Tabela tab = new Tabela("SEG_USUARIOS", BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login,BiblioPMS.Demo));
            tab.adicionarFiltro(new Coluna("COD_USU", DbType.Int32, this.COD_USU));
            tab.delete();
        }
		
		static public DataTable ObterTodos()
        {
            string login = "serix";
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SEG_USUARIOS order by  ";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection)); 
                return tbl; 
                
            }
            catch (Exception e)
            {
 
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }
		
		static public CUsuarios PesquisaporCodigo(string COD_USU, string login)
        {
            CUsuarios obj;
            string SQL = @"SELECT *
                            FROM  Serix.SEG_USUARIOS 
                            WHERE COD_USU = :COD_USU";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.AdicionarParametro(":COD_USU", DbType.String, COD_USU);
               
				qy.Executar();
                obj = new CUsuarios( qy.ObterCampo("COD_USU"), qy.ObterCampo("IDT_SETOR"), qy.ObterCampo("STATUS"), qy.ObterCampo("LOGIN"), qy.ObterCampo("NOME"), qy.ObterCampo("MAIL"), qy.ObterCampo("OBS"), qy.ObterCampo("TELEFONE"), qy.ObterCampo("CARGO"), qy.ObterCampo("SENHA") );
            }                                  
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CUsuarios();
            }
			finally
            {
                qy.FecharConexao();
            }
            return obj;
        }
		
		static public int ObterNovoIdt()
        {
		    string login = "serix";
            string SQL = @"SELECT PKG_BIBSERIX.OBTER_IDT_TER() IDT From dual ";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, login, BiblioPMS.Demo), SQL);
				qy.Executar();
				return Convert.ToInt32(qy.ObterCampo("IDT"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return-1;
            }
			finally
            {
                qy.FecharConexao();
            }
        }
    }
}	

